package com.liliya.dao.impl;

import com.liliya.dao.BaseDao;
import com.liliya.dao.OrderDao;
import com.liliya.pojo.Order;
import com.liliya.pojo.OrderMan;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description TODO
 * @Author hhy
 * @Date 2021/2/21 10:01
 */
public class OrderDaoImpl extends BaseDao implements OrderDao {
    @Override
    public List<Order> findOrders() {
        List<Order> orders = new ArrayList<>();
        String sql = "select ordernum, ordertime, proqnum, proname, pronum, proprice, ordermanid, ok\n" +
                "from T_ORDER where ok=0";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()){
                Order order = new Order();
                order.setOrderNum(rs.getLong("ordernum"));
                order.setOrderTime(rs.getDate("ordertime"));
                order.setProQnum(rs.getLong("proqnum"));
                order.setProName(rs.getString("proname"));
                order.setProNum(rs.getInt("pronum"));
                order.setProPrice(rs.getInt("proprice"));
                order.setOrderMan(null);

                orders.add(order);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        System.out.println(sql);
        return orders;
    }

    @Override
    public List<Order> findOrdersOK() {
        List<Order> orders = new ArrayList<>();
        String sql = "select ordernum, ordertime, proqnum, proname, pronum, proprice, ordermanid, ok\n" +
                "from T_ORDER where ok=1";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()){
                Order order = new Order();
                order.setOrderNum(rs.getLong("ordernum"));
                order.setOrderTime(rs.getDate("ordertime"));
                order.setProQnum(rs.getLong("proqnum"));
                order.setProName(rs.getString("proname"));
                order.setProNum(rs.getInt("pronum"));
                order.setProPrice(rs.getInt("proprice"));
                order.setOrderMan(null);
                orders.add(order);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        System.out.println(sql);
        return orders;
    }

    @Override
    public int total() {
        int total=0;
        String sql = "select sum(1) ss from T_ORDER where ok=0";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()){
               total = rs.getInt("ss");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        System.out.println(sql);
        return total;
    }

    @Override
    public int totalOK() {
        int total=0;
        String sql = "select sum(1) ss from T_ORDER where ok=1";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()){
                total = rs.getInt("ss");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        System.out.println(sql);
        return total;
    }

    @Override
    public List<Order> findOrdersByRow(int start,int end) {
        List<Order> orders = new ArrayList<>();
        String sql = "select ordernum, ordertime, proqnum, proname, pronum, proprice, ordermanid, ok\n" +
                "from (select ordernum, ordertime, proqnum, proname, pronum, proprice, ordermanid, ok,ROWNUM r from T_ORDER where ok=0) where r between ? and ?";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,start);
            pstm.setInt(2,end);
            rs = pstm.executeQuery();
            while (rs.next()){
                Order order = new Order();
                order.setOrderNum(rs.getLong("ordernum"));
                order.setOrderTime(rs.getDate("ordertime"));
                order.setProQnum(rs.getLong("proqnum"));
                order.setProName(rs.getString("proname"));
                order.setProNum(rs.getInt("pronum"));
                order.setProPrice(rs.getInt("proprice"));
                order.setOrderMan(null);

                orders.add(order);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        System.out.println(sql);
        return orders;
    }

    @Override
    public List<Order> findOrdersOKByRow(int start, int end) {
        List<Order> orders = new ArrayList<>();
        String sql = "select ordernum, ordertime, proqnum, proname, pronum, proprice, ordermanid, ok\n" +
                "from (select ordernum, ordertime, proqnum, proname, pronum, proprice, ordermanid, ok,ROWNUM r from T_ORDER where ok=1) where r between ? and ?";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, start);
            pstm.setInt(2, end);
            rs = pstm.executeQuery();
            while (rs.next()) {
                Order order = new Order();
                order.setOrderNum(rs.getLong("ordernum"));
                order.setOrderTime(rs.getDate("ordertime"));
                order.setProQnum(rs.getLong("proqnum"));
                order.setProName(rs.getString("proname"));
                order.setProNum(rs.getInt("pronum"));
                order.setProPrice(rs.getInt("proprice"));
                order.setOrderMan(null);

                orders.add(order);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        System.out.println(sql);
        return orders;
    }

    @Override
    public Order findOrderByOrderNum(Integer orderNum) {
        String sql = "select ordernum, ordertime, proqnum, proname, pronum, proprice, ok, name, city, postcode, addresss, phone, orderway\n" +
                "from T_ORDER o,T_ORDERMAN om where o.ORDERMANID=om.MANID and ORDERNUM=?";

        Order order = null;
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,orderNum);


            rs = pstm.executeQuery();
            while (rs.next()){
                order = new Order();
                OrderMan orderMan = new OrderMan();
                order.setOrderNum(rs.getLong("ordernum"));
                order.setOrderTime(rs.getDate("ordertime"));
                order.setProQnum(rs.getLong("proqnum"));
                order.setProName(rs.getString("proname"));
                order.setProNum(rs.getInt("pronum"));
                order.setProPrice(rs.getInt("proprice"));

                orderMan.setName(rs.getString("name"));
                orderMan.setCity(rs.getString("city"));
                orderMan.setPostCode(rs.getInt("postcode"));
                orderMan.setAddress(rs.getString("addresss"));
                orderMan.setPhone(rs.getLong("phone"));
                orderMan.setOrderWay(rs.getString("orderway"));

                order.setOrderMan(orderMan);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        System.out.println(sql);

        return order;
    }

}
